package com.geline.sqlbackup.util;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.JdbcUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 查询SQL工具类
 * @author: mengx
 * @date: 2021/8/24 20:25
 */
@Slf4j
public class SqlSelectUtils {

    /**
     * 解析查询语句的from表名
     * @param selectSql  查询语句
     * @return
     */
    public static String parseTableName(DbType dbType, String selectSql){
        log.debug("selectSql = {}", selectSql);
        List<SQLStatement> sqlStatements = SQLUtils.parseStatements(selectSql, dbType);
        SQLSelectStatement sqlStatement = (SQLSelectStatement)sqlStatements.get(0);
        SQLSelect select = sqlStatement.getSelect();
        SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) select.getQuery();
        SQLTableSource sqlTableSource = sqlSelectQueryBlock.getFrom();
        String tableName = null;
        if(sqlTableSource instanceof SQLExprTableSource){
            SQLExprTableSource tableSource = (SQLExprTableSource) sqlTableSource;
            tableName = tableSource.getExpr().toString();
        }else if(sqlTableSource instanceof SQLJoinTableSource){
            SQLJoinTableSource tableSource = (SQLJoinTableSource) sqlTableSource;
            SQLTableSource left = tableSource.getLeft();
            SQLExprTableSource leftTableSource = (SQLExprTableSource) left;
            tableName = leftTableSource.getExpr().toString();
        }else {
            throw new RuntimeException("不支持的连表语句，未找到左表信息!");
        }
        return tableName;
    }

    /**
     * 解析查询字段列表
     * @param selectSql  查询语句
     * @return
     */
    public static List<String> parseColumnNameList(DbType dbType, String selectSql){
        List<SQLStatement> sqlStatements = SQLUtils.parseStatements(selectSql, dbType);
        SQLSelectStatement sqlStatement = (SQLSelectStatement)sqlStatements.get(0);
        SQLSelect select = sqlStatement.getSelect();
        SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) select.getQuery();
        List<SQLSelectItem> selectList = sqlSelectQueryBlock.getSelectList();
        return selectList.stream().map(row -> row.getExpr().toString()).collect(Collectors.toList());
    }

    /**
     * 替换SQL动态变量 #{user_name},  #{userName}, ${user_name}, ${userName}
     * @param sqlScript
     * @param key
     * @param value
     * @return
     */
    public static String handleSqlScript(String sqlScript, String key, Object value){
        if(value instanceof Date){
            //传入的查询过滤条件日期全部转为yyyy-MM-dd格式
            value = DateUtil.formatDate((Date) value);
        }
        String sql = sqlScript;
        //替换 like '%#{name}' --->  like '%${name}'
        sql = sql.replace("%#{", "%${");
        //替换变量 #{user_name},  #{userName}, ${user_name}, ${userName}
        String valueScript1 = "#{"+StrUtil.toCamelCase(key)+"}";
        if(value instanceof Collection){
            sql = sql.replace(valueScript1, StringUtils.quotaMarkList((Collection)value));
        }else {
            sql = sql.replace(valueScript1, StringUtils.quotaMark(value));
        }
        String valueScript2 = "#{"+key+"}";
        if(value instanceof Collection){
            sql = sql.replace(valueScript2, StringUtils.quotaMarkList((Collection)value));
        }else {
            sql = sql.replace(valueScript2, StringUtils.quotaMark(value));
        }
        sql = sql.replace(valueScript2, StringUtils.quotaMark(value));
        String valueScript3 = "${"+StrUtil.toCamelCase(key)+"}";
        sql = sql.replace(valueScript3, String.valueOf(value));
        String valueScript4 = "${"+key+"}";
        sql = sql.replace(valueScript4, String.valueOf(value));
        return sql;
    }

    /**
     * 创建 select sql：替换where动态值，如果未传入值去除相关条件
     * 举例：SELECT * FROM sys_menu WHERE parent_id = #{parentId} AND menu_name LIKE '%意见%'
     *  ---> SELECT * FROM sys_menu WHERE 1=1 AND menu_name LIKE '%意见%'
     * 参考：https://www.cnblogs.com/chenatu/p/10794576.html
     * @param sqlScript
     * @param valueMap
     * @param removeNullCondition 是否去除动态值为null的条件
     * @return
     */
    public static String createSelectSQL(DbType dbType, String sqlScript, Map<String, Object> valueMap, boolean removeNullCondition) {
        if(valueMap != null){
            for(String key : valueMap.keySet()){
                Object value = valueMap.get(key);
                if(value != null && !String.valueOf(value).trim().equals("")) {
                    sqlScript = handleSqlScript(sqlScript, key, value);
                }
            }
        }
        //log.debug("old sql >>> {}", sqlScript);
        if(!removeNullCondition){
            return sqlScript;
        }

        try {
            // SQLParserUtils.createSQLStatementParser可以将sql装载到Parser里面
            SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sqlScript, dbType);
            // parseStatementList的返回值SQLStatement本身就是druid里面的语法树对象
            List<SQLStatement> stmtList = parser.parseStatementList();
            if(stmtList != null && stmtList.size()>0){
                SQLStatement stmt = stmtList.get(0);
                if (stmt instanceof SQLSelectStatement) {
                    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
                    // 拿到SQLSelect 通过在这里打断点看对象我们可以看出这是一个树的结构
                    SQLSelect sqlselect = selectStmt.getSelect();
                    SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery();
                    //SQLExpr whereExpr = query.getWhere();

                    //解析where-sql去除空值语句
                    String newWhereSql = parseWhereSQLByRemoveNullValue(dbType, sqlScript);
                    if(StrUtil.isNotBlank(newWhereSql)){
                        SQLExprParser constraintsParser = SQLParserUtils.createExprParser(newWhereSql, JdbcUtils.MYSQL);
                        SQLExpr newWhereExpr = constraintsParser.expr();
                        query.setWhere(newWhereExpr);
                    }

                    String resultSql = sqlselect.toString();
                    if(resultSql.length()<=200){
                        //去除 \n\t 放在一行显示SQL
                        resultSql = resultSql.replaceAll("\\s+"," ");
                    }
                    //log.debug("new sql >>> {}", resultSql);
                    return resultSql;
                } else {
                    throw new RuntimeException("not select statement");
                }
            }
        }catch (ParserException e){
            log.error("error parser sqlScript = {}", sqlScript);
            throw new RuntimeException("sql解析异常，请检查sql语法是否正确！");
        }
        return null;
    }

    public static String parseWhereSQLByRemoveNullValue(DbType dbType, String sqlScript){
        // SQLParserUtils.createSQLStatementParser可以将sql装载到Parser里面
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sqlScript, dbType);
        // parseStatementList的返回值SQLStatement本身就是druid里面的语法树对象
        List<SQLStatement> stmtList = parser.parseStatementList();
        SQLStatement stmt = stmtList.get(0);
        if (stmt instanceof SQLSelectStatement) {
            SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
            // 拿到SQLSelect 通过在这里打断点看对象我们可以看出这是一个树的结构
            SQLSelect sqlselect = selectStmt.getSelect();
            SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery();
            SQLExpr whereExpr = query.getWhere();
            if(whereExpr == null){
                return null;
            }
            String whereSql = whereExpr.toString();
            whereSql = whereSql.replaceAll("\\s+"," ");
            //log.debug("getWhere sql >>> {}", whereSql);

            List<String> conditionList = Arrays.asList(whereSql.split("AND|OR"));
            //log.debug("conditionList >>> {}", conditionList);
            if(conditionList != null && conditionList.size()>0){
                for(String str : conditionList){
                    if(str.contains("#{") || str.contains("${")){
                        str = str.trim();
                        if(str.startsWith("(")){
                            str = str.substring(1);
                        }
                        if(str.endsWith(")")){
                            str = str.substring(0, str.length()-1);
                        }
                        whereSql = whereSql.replace("AND "+str, "");// AND name = #{name1}
                        whereSql = whereSql.replace("OR "+str, "");// OR name = #{name2}
                        whereSql = whereSql.replace(str, "1=1");// name = #{name}
                    }
                }
            }
            whereSql = whereSql.replace("1=1 AND", "")
                    .replaceAll("\\s+"," ")
                    .replace(" )", ")")
                    .replace(" AND (1=1)", "");// AND name = #{name1}
            //log.debug("replace where sql >>>{}", whereSql);
            return whereSql;
        }
        return null;
    }

    public static void main(String[] args) {
        //String sql = "select id, user_name from t_user where createTime=#{createTime} and name=#{userName2} or id in #{idList}";//name = 123 OR id IN (1, 2, 3)
        String sql = "select u.id, u.user_name from t_user u " +
                "left join t_dept t on t.user_id=u.id " +
                "where u.createTime>=#{createTime} and u.user_name=#{userName2} or u.id in #{idList}";//name = 123 OR id IN (1, 2, 3)
        System.out.println("parseColumnNameList = "+parseColumnNameList(DbType.mysql, sql));
        System.out.println("parseTableName = "+parseTableName(DbType.mysql, sql));
        Map<String, Object> valueMap = new HashMap<>();
        valueMap.put("idList", Arrays.asList("1", "2"));
        valueMap.put("userName", "zhangHua");
        valueMap.put("createTime", new Date());
        System.out.println("createSelectSQL = "+createSelectSQL(DbType.mysql, sql, valueMap, true));
    }
}
